package com.ysd.dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import com.ysd.entity.Leansnews;
import com.ysd.util.DBManager;

public class LeansnewsDaoImpl {
	//查询新闻详情
	//访问修饰符 返回值类型 方法名（参数列表）{方法体}
    public List<Leansnews> getLeansnewsList(int cId) {
    	List<Leansnews> leansList = new ArrayList<Leansnews>();//定义一个ArrayList
    	Connection conn= DBManager.getConnection();//获取连接
    	Statement st=null;//执行
    	ResultSet rs=null;//查询
    	
    	try {//正常情况
			st=conn.createStatement();//创建Statement对象，作用是执行SQL语句
			rs=st.executeQuery("select * from leansnews where newsCatalogueId="+cId);//执行查询的SQL语句
			while(rs.next()) {//遍历结果集
				int newsId=rs.getInt("newsId");
				String newsTitle=rs.getString("newsTitle");
				String newsContent=rs.getString("newsContent");
				String newsXiangQin=rs.getString("newsXiangQin");
				int newsCatalogueId=rs.getInt("newsCatalogueId");
				Leansnews leansnews = new Leansnews(newsId, newsTitle, newsContent,newsXiangQin,newsCatalogueId); 
				leansList.add(leansnews);//添加至结果集中
			}
			return leansList;
		} catch (SQLException e) {//非正常情况
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
    
    //后端获取新闻详情
    public List<Leansnews> getLeansLeansnewByWhere(Leansnews l) {
    	List<Leansnews> leansList = new ArrayList<Leansnews>();//定义一个ArrayList
    	Connection conn= DBManager.getConnection();//获取连接
    	Statement st=null;//执行
    	ResultSet rs=null;//查询
    	
    	try {//正常情况
			st=conn.createStatement();//创建Statement对象，作用是执行SQL语句
			String sql="SELECT l.*,n.newsCatalogueName FROM leansnews l,newscatalogue n where 1=1";//执行查询的SQL语句
			//非空判定
			if(l.getNewsId()!=0) {
				sql +=" newsId="+l.getNewsId();
			}
			if(l.getNewsTitle()!=null && !l.getNewsTitle().equals("")) {
				sql +=" and newsTitle LIKE '%"+l.getNewsTitle()+"%' ";
			}
			if(l.getNewsContent()!=null && !l.getNewsContent().equals("")) {
				sql +=" and newsContent LIKE '%"+l.getNewsContent()+"%' ";
			}
			if(l.getNewsXiangQin()!=null && !l.getNewsXiangQin().equals("")) {
				sql +=" and newsXiangQin LIKE '%"+l.getNewsXiangQin()+"%' ";
			}
			if(l.getNewsCatalogueId()!=0) {
				sql +=" and l.newsCatalogueId="+l.getNewsCatalogueId();
			}
			sql +=" and l.newsCatalogueId=n.newsCatalogueId";
			System.out.println(sql);
			
			rs=st.executeQuery(sql);
			while(rs.next()) {//遍历结果集
				int newsId=rs.getInt("newsId");
				String newsTitle=rs.getString("newsTitle");
				String newsContent=rs.getString("newsContent");
				String newsXiangQin=rs.getString("newsXiangQin");
				int newsCatalogueId=rs.getInt("newsCatalogueId");
				String newsCatalogueName=rs.getString("newsCatalogueName");
				Leansnews leansnews = new Leansnews(newsId, newsTitle, newsContent,newsXiangQin,newsCatalogueId,newsCatalogueName); 
				leansList.add(leansnews);//添加至结果集中
			}
			return leansList;
		} catch (SQLException e) {//非正常情况
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
    //分页查询
    public List<Leansnews> getLeansLeansnewByWherePage(Leansnews l,int page,int size) {
    	List<Leansnews> leansList = new ArrayList<Leansnews>();//定义一个ArrayList
    	Connection conn= DBManager.getConnection();//获取连接
    	Statement st=null;//执行
    	ResultSet rs=null;//查询
    	
    	try {//正常情况
			st=conn.createStatement();//创建Statement对象，作用是执行SQL语句
			String sql="SELECT l.*,n.newsCatalogueName FROM leansnews l,newscatalogue n where 1=1";//执行查询的SQL语句
			//非空判定
			if(l.getNewsId()!=0) {
				sql +=" newsId="+l.getNewsId();
			}
			if(l.getNewsTitle()!=null && !l.getNewsTitle().equals("")) {
				sql +=" and newsTitle LIKE '%"+l.getNewsTitle()+"%' ";
			}
			if(l.getNewsContent()!=null && !l.getNewsContent().equals("")) {
				sql +=" and newsContent LIKE '%"+l.getNewsContent()+"%' ";
			}
			if(l.getNewsXiangQin()!=null && !l.getNewsXiangQin().equals("")) {
				sql +=" and newsXiangQin LIKE '%"+l.getNewsXiangQin()+"%' ";
			}
			if(l.getNewsCatalogueId()!=0) {
				sql +=" and l.newsCatalogueId="+l.getNewsCatalogueId();
			}
			sql +=" and l.newsCatalogueId=n.newsCatalogueId limit "+(page-1)*size+","+size+"";
			System.out.println(sql);
			
			rs=st.executeQuery(sql);
			while(rs.next()) {//遍历结果集
				int newsId=rs.getInt("newsId");
				String newsTitle=rs.getString("newsTitle");
				String newsContent=rs.getString("newsContent");
				String newsXiangQin=rs.getString("newsXiangQin");
				int newsCatalogueId=rs.getInt("newsCatalogueId");
				String newsCatalogueName=rs.getString("newsCatalogueName");
				Leansnews leansnews = new Leansnews(newsId, newsTitle, newsContent,newsXiangQin,newsCatalogueId,newsCatalogueName); 
				leansList.add(leansnews);//添加至结果集中
			}
			return leansList;
		} catch (SQLException e) {//非正常情况
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
    /***
             * 删除新闻详情
     * @param int是返回受影响行数
     * @return
     */
    public int delNews(int id) {
    	return DBManager.executeUpdate("DELETE FROM leansnews WHERE newsId="+id);
    }
    /****
     * 添加新闻详情
     * @param返回受影响行数
     * @return
     */
    public int addNews(Leansnews nc) {
    	return DBManager.executeUpdate("insert into leansnews values(null,'"+nc.getNewsTitle()+"','"+nc.getNewsContent()+"','"+nc.getNewsXiangQin()+"','"+nc.getNewsCatalogueId()+"')");
    }
    /***
     * 根据名字查询标题
     * @param name
     * @return
     */
    public List<Leansnews> getNewsByTitle(String name){
    	List<Leansnews> ncList = new ArrayList<Leansnews>();
    	Connection conn=DBManager.getConnection();
    	Statement st=null;
    	ResultSet rs=null;
    	try {
			st=conn.createStatement();
			rs=st.executeQuery("select * from leansnews where newsTitle='"+name+"'");
			while(rs.next()) {
				int newsId=rs.getInt("newsId");
				String newsTitle=rs.getString("newsTitle");
				String newsContent=rs.getString("newsContent");
				String newsXiangQin=rs.getString("newsXiangQin");
				int newsCatalogueId=rs.getInt("newsCatalogueId");
				Leansnews nc=new Leansnews(newsId, newsTitle, newsContent, newsXiangQin, newsCatalogueId);
				ncList.add(nc);
			}
			return ncList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
    //通过id获取其他内容
   public Leansnews getLeansnewsById(int lnId) {
	   Leansnews ln=null;
	   Connection conn=DBManager.getConnection();
	   Statement st=null;
	   ResultSet rs=null;
	   try {
		st=conn.createStatement();
		rs=st.executeQuery("SELECT * FROM leansnews WHERE newsId="+lnId);
		while(rs.next()) {
			int nId=rs.getInt(1);
			String nTitle=rs.getString(2);
			String nContent=rs.getString(3);
			String newsCatalogueId=rs.getString(5);
			ln=new Leansnews(nId, nTitle, nContent,newsCatalogueId);
		}
		return ln;
	} catch (SQLException e) {
		e.printStackTrace();
		return null;
	}finally {
		DBManager.closeAll(conn, st, rs);
	}
   }
   
   /***
    * 修改新闻详情
    */
   public int updateLeansnewsById(Leansnews ln) {
	   String sql=("UPDATE leansnews set newsTitle='"+ln.getNewsTitle()+"', newsContent='"+ln.getNewsContent()+"',newsCatalogueId="+ln.getNewsCatalogueId()+" WHERE newsId="+ln.getNewsId()+"");
	   System.out.print(sql);
	   return DBManager.executeUpdate(sql);
   }
    //批量删除
   //
   public int delectProductByIds(String ids) {
		 String sql="DELETE FROM leansnews WHERE newsId in("+ids+")";
		 System.out.println(sql);
		 return DBManager.executeUpdate(sql);
	}
    //主页新闻案例
  	//访问修饰符 返回值类型 方法名（参数列表）{方法体}
    public List<Leansnews> getLeansnewList(int cpId) {
    	List<Leansnews> leansList = new ArrayList<Leansnews>();//定义一个ArrayList
    	Connection conn = (Connection) DBManager.getConnection();//创建连接
    	Statement st=null;
    	ResultSet rs=null;    	
    	try {//正常情况
			st=conn.createStatement();//创建Statement对象，作用是执行SQL语句
			rs=st.executeQuery("select * from leansnews where newsId="+cpId);//执行SQL语句
			while(rs.next()) {//遍历结果集
				int newsId=rs.getInt("newsId");
				String newsTitle=rs.getString("newsTitle");
				String newsContent=rs.getString("newsContent");
				String newsXiangQin=rs.getString("newsXiangQin");
				int newsCatalogueId=rs.getInt("newsCatalogueId");
				Leansnews leansnews =new Leansnews(newsId, newsTitle, newsContent, newsXiangQin, newsCatalogueId);
				 leansList.add(leansnews);
			}
			return leansList;
		} catch (SQLException e) {//非正常情况
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
    }
}
